const express = require('express');
const router = express.Router();
const $sql = require('../db/sqlMap');
const auth = require('../middleware/auth');

function sep() {
	console.log("=".repeat(50) + '\n');
}

router.post('/show', (req, res) => {
	const sel_all = $sql.main.select;
	conn.query(sel_all, (error, results) => {
		if (error) {
			// throw error;
			console.log('数据库错误');
			console.log(error); sep();
			res.status(500).json({
				statusCode: -1,
				message: "新闻主表查询错误"
			})
		} else {
			console.log('显示新闻数据成功。'); sep();
			res.json({
				statusCode: 1,
				message: "显示全部新闻成功",
				data: results
			});
		}
	})
});

router.post('/edit', auth, (req, res) => {
	const news = req.body;
	const edit_main = $sql.main.edit + news.name + " = ? where news_id = ?";
	console.log('编辑请求参数：', news);
	const update_data = [
		news.cont,
		parseInt(news.news_id),
	];
	try {
		let a = req.user
			.then(ress => {
				var restr = JSON.stringify(ress);
				var redata = JSON.parse(restr)[0];
				console.log('Token对应用户id查询结果:', redata);
				if (redata == undefined || redata.user_id == undefined) {
					console.log('无效的Token'); sep();
					res.status(401).json({
						statusCode: -3,
						message: "无效的Token，找不到对应用户"
					})
				} else if (redata.power == 'normal') {
					console.log('没有权限编辑。'); sep();
					res.status(403).json({
						statusCode: 0,
						message: "没有权限编辑"
					})
				} else {
					conn.query(edit_main, update_data, (err, results) => {
						if (err) {
							// throw err;
							console.log('数据库错误');
							console.log(err); sep();
							res.status(500).json({
								statusCode: -1,
								message: "写入新闻主表过程中出错。"
							})
						} else {
							console.log('编辑新闻成功。'); sep();
							// console.log(news);
							res.json({
								statusCode: 1,
								message: "编辑成功"
							});
						}
					})
				}
			})
			.catch(err => {
				// .catch 返回报错信息
				console.error('鉴权出错');
				console.error(err); sep();
				res.status(500).json({
					statusCode: -1,
					message: "服务端鉴权出错。"
				})
			})
	} catch (e) {
		console.error(e); sep();
		res.status(401).json({
			statusCode: -2,
			message: "未登录"
		})
	}
});

router.post('/simple_search', (req, res) => {
	const sel_cont = req.body;
	console.log('普通单查询信息为：', sel_cont);
	var sel_main_cont = $sql.main.simple_search;
	if (sel_cont.category != null && sel_cont.category != '') sel_main_cont += (" and category = '" + sel_cont.category + "'");
	if (sel_cont.sentiment != null && sel_cont.sentiment != '') sel_main_cont += (" and sentiment = '" + sel_cont.sentiment + "'");
	if (sel_cont.startTime != null && sel_cont.startTime != '') sel_main_cont += (" and DATE(publish_time) >= DATE('" + sel_cont.startTime + "')");
	if (sel_cont.endTime != null && sel_cont.endTime != '') sel_main_cont += (" and DATE(publish_time) <= DATE('" + sel_cont.endTime + "')");
	sel_main_cont += ' order by news_id desc';

	// console.log(sel_main_cont);
	conn.query(sel_main_cont, (err, results) => {
		if (err) {
			console.error("普通查询数据库出错");
			console.error(err); sep();
			res.status(500).json({
				statusCode: -1,
				message: "普通查询数据库出错"
			})
		} else {
			console.log('普通查询成功.'); sep();
			res.json({
				statusCode: 1,
				message: "普通查询成功",
				data: results
			});
		}
	})
})

router.post('/advanced_search', (req, res) => {
	const part = req.body;
	console.log('高级查询信息为：', part);
	var key_lst = part.keywords.split(/\s+/g);
	const len = key_lst.length;
	var sel_sql = $sql.main.advanced_search;
	for (var i = 0; i < len; i++) {
		sel_sql += " and keywords regexp '" + key_lst[i] + "'";
	}
	sel_sql += " order by news_id desc";

	conn.query(sel_sql, (error, results) => {
		if (error) {
			console.error("高级查询数据库出错");
			console.error(error); sep();
			res.status(500).json({
				statusCode: -1,
				message: "高级查询数据库出错"
			})
		} else {
			console.log('高级查询成功'); sep();
			res.json({
				statusCode: 1,
				message: "高级查询成功",
				data: results
			})
		}
	})
});

//热点新闻概览
router.post('/hotnewsview', (req, res) => {
	const hotview_sql = $sql.main.newsview + "where keywords REGEXP (select Hotwords from hotwords order by score desc limit 1) limit 10";

	conn.query(hotview_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})
});

//各大新闻网概览信息
router.post('/newsoverview', (req, res) => {
	const sel_req = req.body;
	const overview_sql = $sql.main.newsview + "where publish_time >= \'" + sel_req.beginTime + "\' and publish_time <= \'" + sel_req.endTime + "\'";
	//DATE的问题
	conn.query(overview_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})

});

//发稿量 news_count
router.post('/newscount', (req, res) => {
	const news = req.body;
	//console.log(news);
	var begin_time = new Date(news.begin_time);
	var tomorrow_time;
	var end_time = new Date(news.end_time);

	const becount_main = $sql.main.daycount;

	var json_results = [];

	for (begin_time; begin_time < end_time;) {

		tomorrow_time = new Date(new Date().setDate(begin_time.getDate() + 1));

		const onedate_result = [
			begin_time,
			begin_time,
			tomorrow_time
		];
		conn.query(becount_main, onedate_result, (err, result) => {
			if (err) {
				throw err;
			} else {
				json_results.push(result);
			}

		})
		begin_time = tomorrow_time;

	}
	setTimeout(function () { console.log(json_results); res.send(json_results); }, 1000);
});


//专题/热词信息
router.post('/topic_hotwords', (req, res) => {
	const th_req = req.body;
	const th_sql = $sql.main.topic_hotwords + th_req.parameter;

	conn.query(th_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})

});

//分类/情感概览
router.post('/category_sentiment', (req, res) => {
	const cs_req = req.body;
	const cs_sql = "select count(" + cs_req.parameter + ") as value , " + cs_req.parameter + " as name from main group by " + cs_req.parameter;

	conn.query(cs_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})
});

//热词搜索 hw_search
router.post('/hw_search', (req, res) => {
	const hw_req = req.body;
	const hw_sql = $sql.main.newsview + "where keywords REGEXP \'" + hw_req.Hotwords + "\'";

	conn.query(hw_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})
})

//专题搜索
router.post('/st_search', (req, res) => {
	const sts_req = req.body;
	const sts_sql = $sql.main.newsview + "where news_id = some(select news_id from main_topic,topic where specialtopic = ? and main_topic.s_topic_id=topic.s_topic_id)";

	const st = [sts_req.specialtopic];
	conn.query(sts_sql, st, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})
});

//分类/情感搜索
router.post('/cs_search', (req, res) => {
	const css_req = req.body;
	const css_sql = $sql.main.newsview + "where " + css_req.c_or_s + " = \"" + css_req.parameter + "\"";

	conn.query(css_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log(results);
		res.json(results);
	})
});

//库存信息
router.post('/inventory', (req, res) => {
	const invent_req = req.body;

	var date = new Date(invent_req.time);

	var year = date.getFullYear().toString();
	var month = date.getMonth() + 1 < 10 ? '0' + (date.getMonth() + 1) : date.getMonth();
	var day = date.getDate() + 1 < 10 ? '0' + (date.getDate() + 1) : date.getDate() - 1;
	var ymd = year + "-" + month + "-01 00:00:00";
	var yesterday = year + "-" + month + "-" + day + " 00:00:00";
	//console.log('yesterday:',yesterday);

	var total_invent_sql = $sql.main.total_inventory;
	var today_invent_sql = $sql.main.today_inventory + "where publish_time >= '" + invent_req.time + "'";
	var yesterday_invent_sql = $sql.main.yesterday_inventory + "where publish_time <= '" + invent_req.time + "' and publish_time >= '" + yesterday + "'";
	var month_invent_sql = $sql.main.month_inventory + "where publish_time >= '" + ymd + "'";
	var rsu = {};

	conn.query(total_invent_sql, (error, results) => {
		if (error) {
			throw error;
		}
		console.log('第一条查询语句', results);
		rsu.total_inventory = results[0].a;
	});

	conn.query(month_invent_sql, (error, results1) => {
		if (error) {
			throw error;
		}
		console.log('第二条查询语句', results1);
		rsu.month_inventory = results1[0].c;
	});

	conn.query(yesterday_invent_sql, (error, results2) => {
		if (error) {
			throw error;
		}
		console.log('第三条查询语句', results2);
		rsu.yesterday_inventory = results2[0].d;
	});

	conn.query(today_invent_sql, (error, results3) => {
		if (error) {
			throw error;
		}
		console.log('第四条查询语句', results3);
		// JSON.parse(results2);
		rsu.today_new_inventory = results3[0].b;
		console.log('rsu:', rsu);
		res.json(rsu);
	});
})

module.exports = router;